/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package persistence.services;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.logging.Log;
import utils.SmLogFactory;
import utils.SmProperties;

/**
 *
 * @author jmoura
 */
public class SmDatabase implements Serializable {

    private static Connection conn = null;
    private static final Log log = SmLogFactory.getLog(SmDatabase.class);

    /**
     * Creates a new instance of SysBanco
     */
    public SmDatabase() {
        if (getConn() == null) {
            criaConn();
        }
    }

    public synchronized static Connection getConn() {
        if (conn == null) {
            criaConn();
        }
        return conn;
    }

    public static void setConn(Connection c) {
        conn = c;
    }

    private static void criaConn() {
        try {
            //load configs
            //"jdbc:postgresql://10.1.7.19:5432/SmartProjects";
            final String connectionString = String.format("jdbc:%s://%s:%s/%s",
                    SmProperties.getProperty("dbDatabaseType"),
                    SmProperties.getProperty("dpIpAddress"),
                    SmProperties.getProperty("dbPortAddress"),
                    SmProperties.getProperty("dbDatabase"));
            final String user = SmProperties.getProperty("dbUser");
            final String password = SmProperties.getProperty("dbPassword");
            final String driver = SmProperties.getProperty("dbDriver");

            log.debug("Connecting to DB: " + connectionString);
            //test if the driver is there.
            Class.forName(driver);
            //connect to DB
            conn = DriverManager.getConnection(connectionString, user, password);
            if (conn != null) {
                log.debug("Successfully connected to Postgres Database");
            } else {
                log.debug("We should never get here.");
            }
        } catch (SQLException ex) {
            log.error("Cannot access the DB", ex);
            throw new UnsupportedOperationException("Full Stop!");
        } catch (ClassNotFoundException ex) {
            log.error("Couldn't find driver class:", ex);
            throw new UnsupportedOperationException("Full Stop!");
        }
    }

    public static void cleanUp(PreparedStatement pstm, ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (pstm != null) {
            pstm.close();
        }
    }

    public static void cleanUp(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    public static void cleanUp(PreparedStatement pstm) throws SQLException {
        if (pstm != null) {
            pstm.close();
        }
    }

    public static PreparedStatement prepareStatement(String sql) throws SQLException {
        if (SmProperties.getProperty("dbDatabaseType").equalsIgnoreCase("postgresql")) {
            return SmDatabase.getConn().prepareStatement(sql);
        } else if (SmProperties.getProperty("dbDatabaseType").equalsIgnoreCase("mysql")) {
            return SmDatabase.getConn().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        }
        return SmDatabase.getConn().prepareStatement(sql);
    }

    /**
     * for autoincrement columns for MySql
     *
     * @param pstm
     * @return
     * @throws SQLException
     */
    public static int getLastInsertedId(PreparedStatement pstm) throws SQLException {
        int ret = 0;
        ResultSet rs = pstm.getGeneratedKeys();
        if (rs.next()) {
            ret = rs.getInt(1);
        }
        SmDatabase.cleanUp(rs);
        return ret;
    }

    public int getSequenceNextval(String sequenceName) {
        int ret = 0;
        try {
            PreparedStatement pstm = getConn().prepareStatement("SELECT NEXTVAL('" + sequenceName + "'::regclass)");
            ResultSet rs = pstm.executeQuery();
            if (rs.next()) {
                ret = rs.getInt(1);
            }
            cleanUp(pstm, rs);
        } catch (SQLException ex) {
            log.error("Db Trouble...", ex);
        }
        return ret;
    }
}
